Project : Wrangle and Analyze Data¶
by Saravanan Natarajan
Introduction:¶
Real-world data rarely comes clean. Using Python and its libraries, you will gather data from a variety of sources and in a variety of formats, assess its quality and tidiness, then clean it. This is called data wrangling.
Wrangling involves Wow- worthy analyses and visualizations.
Import libraries¶
In [2]:
import pandas as pd
import datetime as dt
import numpy as np
import requests
import sqlalchemy
import tweepy
import json
import re
import time
from PIL import Image
from io import BytesIO
Create functions for Programmatic assessment¶
In [3]:
def get_df_list(df):
return list(df)
def get_df_info(df):
df.info()
def get_df_describe(df):
return df.describe()
def get_df_sample(df, sample_value):
return df.sample(sample_value)
def get_df_value_counts(df):
return df.value_counts()
def get_df_len(df):
return len(df)
def get_df_head(df, count):
return df.head(count)
Gathering Data¶
Data from WeRateDogs Twitter archive¶
In [4]:
df_weratedogs_archive = pd.read_csv('twitter-archive-enhanced.csv')
Data from Neural Network based image prediction¶
In [5]:
udacity_url = 'https://d17h27t6h515a5.cloudfront.net/topher/2017/August/599fd2ad_image-predictions/image-predictions.tsv'
url_response = requests.get(udacity_url)
with open(udacity_url.split('/')[-1], mode='wb') as file:
file.write(url_response.content)
df_nn_image_predictions = pd.read_csv('image-predictions.tsv', sep='\t')
In [144]:
access_token = "YOUR ACCESS TOKEN"
access_token_secret = "YOUR ACCESS SECRET"
consumer_key = "YOUR CONSUMER KEY"
consumer_secret = "YOUR CONSUMER SECRET"
Tweepy to query JSON data from Twitter¶
In [8]:
auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api = tweepy.API(auth, parser=tweepy.parsers.JSONParser(),
wait_on_rate_limit=True,
wait_on_rate_limit_notify = True)
Extract JSON data from Twitter API¶
In [9]:
get_df_head(df_weratedogs_archive, 2)
Out[9]:
Interested in additional data such as date and time of the creation of the tweet and utility used to post the tweet.
In [10]:
df_list = []
error_list = []
start = time.time()
for tweet_id in df_weratedogs_archive.tweet_id:
try:
page = api.get_status(tweet_id, tweet_mode = 'extended')
favorites = page['favorite_count']
retweets = page['retweet_count']
date_time = page['created_at']
source = page['source']
df_list.append({'tweet_id': int(tweet_id),
'favorites': int(favorites),
'retweets': int(retweets),
'date_time': pd.to_datetime(date_time),
'utility_source' : str(source)})
except Exception as e:
print(str(tweet_id)+ " _ " + str(e))
error_list.append(tweet_id)
end = time.time()
print(end - start)
The tweets corresponding to a few tweet IDs in the archive may have been deleted were captured in error_list
In [11]:
error_list
Out[11]:
In [13]:
print("The lengh of the result", len(df_list))
print("The lengh of the errors", len(error_list))
From the above results:
- We reached the limit of the Tweepy API twice but wait_on_rate_limit automatically wait for rate limits to replenish and wait_on_rate_limit_notify print a notification when Tweepy is waiting
- The total time was about 2221.381744861603 seconds (~ 37 min)
- We could get 2345 tweet_id correctly with 11 were deleted (we will query those 11 errors separately)
In [14]:
ee_list = []
for e in error_list:
try:
favorites = page['favorite_count']
retweets = page['retweet_count']
date_time = page['created_at']
source = page['source']
df_list.append({'tweet_id': int(tweet_id),
'favorites': int(favorites),
'retweets': int(retweets),
'date_time': pd.to_datetime(date_time),
'utility_source' : str(source)})
except Exception:
print(str(tweet_id)+ " _ " + str(e))
ee_list.append(e)
In [15]:
print("The lengh of the result after Querying the errors separately", len(df_list))
print("The lengh of the errors", len(ee_list))
During cleaning we can remove the error list
Write JSON data in text file¶
In [16]:
df_json_tweets = pd.DataFrame(df_list, columns = ['tweet_id', 'favorites', 'retweets',
'date_time', 'utility_source'])
df_json_tweets.to_csv('tweet_json.txt', encoding = 'utf-8', index=False)
In [17]:
df_json_tweets = pd.read_csv('tweet_json.txt', encoding = 'utf-8')
get_df_head(df_json_tweets, 5)
Out[17]:
The file tweet_json.txt successfully saved in our working directory contains the result of the API Querying then stored in df_json_tweets data frame, it has 6 columns and 2356 entries
Gather: Summary¶
Gathering is the first step in the data wrangling process. We could finish the high-level gathering process:
Obtaining data
- Getting data from an existing file (twitter-archive-enhanced.csv) Reading from csv file using pandas
- Downloading a file from the internet (image-predictions.tsv) Downloading file using requests
- Querying an API (tweet_json.txt) Get JSON object of all the tweet_ids using Tweepy
- Importing that data into our programming environment (Jupyter Notebook)
Assessing Data¶
Assess WeRateDogs Twitter archive¶
Programmatic assessment of WeRateDogs archive¶
In [18]:
get_df_list(df_weratedogs_archive)
Out[18]:
In [19]:
get_df_info(df_weratedogs_archive)
In [20]:
get_df_describe(df_weratedogs_archive)
Out[20]:
In [21]:
get_df_sample(df_weratedogs_archive, 7)
Out[21]:
WeRateDogs is popular because of the rating system, we will view rating numerators and denominators
In [146]:
get_df_value_counts(df_weratedogs_archive.rating_numerator)
Out[146]:
In [147]:
get_df_value_counts(df_weratedogs_archive.rating_denominator)
Out[147]:
In [22]:
get_df_value_counts(df_weratedogs_archive.name)
Out[22]:
Randomly assess the data
In [148]:
print(df_weratedogs_archive.text[800])
print(df_weratedogs_archive.name[220])
In [149]:
print(df_weratedogs_archive[df_weratedogs_archive.rating_numerator == 0])
Programmatic assessment image prediction data frame¶
In [26]:
get_df_list(df_nn_image_predictions)
Out[26]:
In [27]:
get_df_info(df_nn_image_predictions)
In [28]:
get_df_describe(df_nn_image_predictions)
Out[28]:
In [29]:
get_df_sample(df_nn_image_predictions, 3)
Out[29]:
In [30]:
get_df_value_counts(df_nn_image_predictions.p1_conf)
Out[30]:
In [240]:
df_nn_image_predictions[df_nn_image_predictions['jpg_url'] ==
'https://pbs.twimg.com/media/CZhn-QAWwAASQan.jpg']
Out[240]:
In [241]:
print(df_nn_image_predictions [df_nn_image_predictions.tweet_id.isnull()])
print(df_nn_image_predictions[df_nn_image_predictions.tweet_id.duplicated()])
Based on reference we further assess the prediction data
In [150]:
envelope_image_url = df_nn_image_predictions.jpg_url[df_nn_image_predictions.
tweet_id==860924035999428608].iloc[0]
request_image = requests.get(envelope_image_url)
image = Image.open(BytesIO(request_image.content))
image
Out[150]:
At p1 it was predicted as envelope, then oscilloscope and finally paper_towel. Same we can try for box_turtle below
In [247]:
envelope_image_url = df_nn_image_predictions.jpg_url[df_nn_image_predictions.
tweet_id==870063196459192321].iloc[0]
request_image = requests.get(envelope_image_url)
image = Image.open(BytesIO(request_image.content))
image
Out[247]:
Even Clifford was added in prediction
In [248]:
box_turtle_image_url = df_nn_image_predictions.jpg_url[df_nn_image_predictions.
tweet_id==880935762899988482].iloc[0]
request_image_box_turtle = requests.get(box_turtle_image_url)
image = Image.open(BytesIO(request_image_box_turtle.content))
image
Out[248]:
Prediction involves picture and real dog
In [151]:
box_turtle_image_url = df_nn_image_predictions.jpg_url[df_nn_image_predictions.
tweet_id==666051853826850816].iloc[0]
request_image_box_turtle = requests.get(box_turtle_image_url)
image = Image.open(BytesIO(request_image_box_turtle.content))
image
Out[151]:
It's a turtle
Programmatic assessment of twitter JSON¶
In [35]:
get_df_list(df_json_tweets)
Out[35]:
In [36]:
get_df_info(df_json_tweets)
In [37]:
get_df_sample(df_json_tweets, 5)
Out[37]:
Identify duplicates in all three dataframes
In [152]:
df_all_columns = pd.Series(list(df_weratedogs_archive) +
list(df_nn_image_predictions) +
list(df_weratedogs_archive))
df_all_columns[df_all_columns.duplicated()]
Out[152]:
Quality¶
Assess data quality and tidiness
WeRateDogs Archive table¶
- retweeted_status_timestamp, timestamp should be datetime instead of object (string)
- name columns can't possibly be dog names. (a, an...)
- Missing expanded_urls
- The numerator and denominator columns have invalid values
- In several columns null objects are non-null (None to NaN)
- Want only original ratings (no retweets) that have images
Image prediction table¶
- Lowercase given p1, p2, p3
- missing data (only has 2075 entries instead of 2356)
Twitter JSON table¶
- This tweet_id (666020888022790149) duplicated 12 times
Tidiness¶
(structural issues)
WeRateDogs Archive,Image predictionandTwitter JSONtables should be part of one dataframe- Add a gender column from the text columns in
WeRateDogs Archivedataframe - 'doggo','floofer', 'pupper' and 'puppo' in
WeRateDogs Archivedataframe can have a common name.
Cleaning Data¶
In [200]:
df_clean_weratedogs_archive = df_weratedogs_archive.copy()
df_clean_nn_image_predictions = df_nn_image_predictions.copy()
df_clean_json_tweets = df_json_tweets.copy()
Define¶
Change timestamp and retweeted_status_timestamp to datetimes
Code¶
In [201]:
df_clean_weratedogs_archive.timestamp = pd.to_datetime(
df_clean_weratedogs_archive.timestamp)
df_clean_weratedogs_archive.retweeted_status_timestamp = pd.to_datetime(
df_clean_weratedogs_archive.retweeted_status_timestamp)
Test¶
In [202]:
get_df_info(df_clean_weratedogs_archive)
Define¶
name columns can't possibly be dog names. (a, an ...)
Code¶
In [203]:
wrong_names = []
for idx, name in enumerate(df_clean_weratedogs_archive.name):
if name.islower() or name == 'None':
j = re.compile('(?:named\s|name\sis\s)(\w+)')
if j.findall(df_clean_weratedogs_archive.loc[idx,'text']):
df_clean_weratedogs_archive.loc[idx,'name'] = j.findall(
df_clean_weratedogs_archive.loc[idx,'text'])[0]
else:
df_clean_weratedogs_archive.loc[idx,'name'] = 'None'
Test¶
In [204]:
get_df_value_counts(df_clean_weratedogs_archive.name)
Out[204]:
In the df_weratedogs_archive there were name "a" count 55 and "an" count 7 after cleaning the data there is no more "a" and "an"
Define¶
missing expanded_urls
Code¶
In [205]:
def add_urls(row):
if pd.notnull(row['expanded_urls']):
return row
else:
tweet_id = row['tweet_id']
row['expanded_urls'] = 'https://twitter.com/dog_rates/status/{}'.format(
tweet_id)
return row
df_clean_weratedogs_archive = df_clean_weratedogs_archive.apply(add_urls, axis=1)
Test¶
In [206]:
df_clean_weratedogs_archive[df_clean_weratedogs_archive['expanded_urls'].isnull()]
Out[206]:
In df_clean_weratedogs_archive there is no null expanded_urls
Define¶
The numerator and denominator columns have invalid values. Replace ratings manually by looking at text from tweet
Code¶
In [207]:
problem_entries_indexes = [x-1 for x in [1069, 1166, 2336]]
problem_entries = df_clean_weratedogs_archive.iloc[problem_entries_indexes]
for txt in problem_entries.text:
print(txt)
df_clean_weratedogs_archive.at[problem_entries_indexes[0],
'rating_numerator'] = 14
df_clean_weratedogs_archive.at[problem_entries_indexes[0],
'rating_denominator'] = 10
df_clean_weratedogs_archive.at[problem_entries_indexes[1],
'rating_numerator'] = 13
df_clean_weratedogs_archive.at[problem_entries_indexes[1],
'rating_denominator'] = 10
df_clean_weratedogs_archive.at[problem_entries_indexes[2],
'rating_numerator'] = 9
df_clean_weratedogs_archive.at[problem_entries_indexes[2],
'rating_denominator'] = 10
Test¶
In [208]:
problem_entries = df_clean_weratedogs_archive.iloc[problem_entries_indexes]
values = zip(problem_entries.text, problem_entries.rating_numerator,
problem_entries.rating_denominator)
for (txt, n, d) in values:
print('{}/{} from: \n\t"{}"'.format(n,d,txt))
Define¶
Make all strings in p1, p2, and p3 lowercase
Code¶
In [209]:
df_clean_nn_image_predictions['p1'] = df_clean_nn_image_predictions['p1'].str.lower()
df_clean_nn_image_predictions['p2'] = df_clean_nn_image_predictions['p2'].str.lower()
df_clean_nn_image_predictions['p3'] = df_clean_nn_image_predictions['p3'].str.lower()
Test¶
In [210]:
get_df_head(df_clean_nn_image_predictions,2)
Out[210]:
Cleaning our data is the third step in data wrangling. It is where we will fix the quality and tidiness issues that we identified in the assess step.
In [211]:
df_weratedogs_master = pd.merge(df_clean_weratedogs_archive,
df_clean_nn_image_predictions,
how = 'left', on = ['tweet_id'] )
df_weratedogs_master = pd.merge(df_weratedogs_master,
df_clean_json_tweets,
how = 'left', on = ['tweet_id'])
df_weratedogs_master.to_csv('weratedogs_master.csv', encoding = 'utf-8')
get_df_info(df_weratedogs_master)
Define¶
Delete the retweets, delete duplicated tweet_id and delete tweets with no pictures
Code¶
In [212]:
df_weratedogs_master = df_weratedogs_master[pd.isnull(
df_weratedogs_master.retweeted_status_id)]
df_weratedogs_master = df_weratedogs_master.drop_duplicates()
df_weratedogs_master = df_weratedogs_master.dropna(subset = ['jpg_url'])
Test¶
In [213]:
get_df_len(df_weratedogs_master)
Out[213]:
Define¶
Drop columns related to retweet and drop column date_time we imported from the API
Code¶
In [214]:
df_weratedogs_master = df_weratedogs_master.drop('retweeted_status_id', 1)
df_weratedogs_master = df_weratedogs_master.drop('retweeted_status_user_id', 1)
df_weratedogs_master = df_weratedogs_master.drop('retweeted_status_timestamp', 1)
df_weratedogs_master = df_weratedogs_master.drop('date_time', 1)
Test¶
In [215]:
get_df_list(df_weratedogs_master)
Out[215]:
Code¶
In [216]:
dog_breed = []
conf = []
def breed_conf (row):
if row['p1_dog']:
dog_breed.append(row['p1'])
conf.append(row['p1_conf'])
elif row['p2_dog']:
dog_breed.append(row['p2'])
conf.append(row['p2_conf'])
elif row['p3_dog']:
dog_breed.append(row['p3'])
conf.append(row['p3_conf'])
else:
dog_breed.append('Unidentifiable')
conf.append(0)
df_weratedogs_master.apply(breed_conf, axis = 1)
df_weratedogs_master['dog_breed'] = dog_breed
df_weratedogs_master['confidence'] = conf
In [217]:
df_weratedogs_master.drop(['p1', 'p1_conf', 'p1_dog',
'p2', 'p2_conf', 'p2_dog',
'p3', 'p3_conf', 'p3_dog',],
axis=1, inplace=True)
Test¶
In [218]:
get_df_info(df_weratedogs_master)
In [219]:
get_df_value_counts(df_weratedogs_master.source)
Out[219]:
The result of the above lines says:
source has 3 types, we will clean that column and made them clear
Define¶
Drop the following columns 'in_reply_to_status_id', 'in_reply_to_user_id', 'utility_source' clean the content of source column
Code¶
In [220]:
df_weratedogs_master = df_weratedogs_master.drop(
['in_reply_to_status_id', 'in_reply_to_user_id',
'utility_source'], 1)
df_weratedogs_master['source'] = df_weratedogs_master['source'].apply(
lambda x: re.findall(r'>(.*)<', x)[0])
Test¶
In [221]:
get_df_head(df_weratedogs_master,2)
Out[221]:
Define¶
'doggo','floofer', 'pupper' and 'puppo' in WeRateDogs Archive dataframe can have a common name.
Code¶
In [222]:
dog_type = []
x = ['pupper', 'puppo', 'doggo', 'floof']
y = ['pupper', 'puppo', 'doggo', 'floof']
for row in df_weratedogs_master['text']:
row = row.lower()
for word in x:
if word in str(row):
dog_type.append(y[x.index(word)])
break
else:
dog_type.append('None')
df_weratedogs_master['dog_type'] = dog_type
In [223]:
df_weratedogs_master.drop(['doggo', 'floofer', 'pupper', 'puppo'],
axis=1, inplace=True)
Test¶
In [224]:
get_df_info(df_weratedogs_master)
Define¶
Add dog_names column
Code¶
In [225]:
dog_names = []
for text in df_weratedogs_master['text']:
if text.startswith('This is ') and re.match(r'[A-Z].*',
text.split()[2]):
dog_names.append(text.split()[2].
strip(',').
strip('.'))
elif text.startswith('Meet ') and re.match(r'[A-Z].*',
text.split()[1]):
dog_names.append(text.split()[1].
strip(',').
strip('.'))
elif text.startswith('Say hello to ') and re.match(r'[A-Z].*',
text.split()[3]):
dog_names.append(text.split()[3].
strip(',').
strip('.'))
elif text.startswith('Here we have ') and re.match(r'[A-Z].*',
text.split()[3]):
dog_names.append(text.split()[3].
strip(',').
strip('.'))
elif 'named' in text and re.match(r'[A-Z].*',
text.split()[text.split().
index('named') + 1]):
dog_names.append(text.split()[text.split().index('named') + 1].
strip(',').
strip('.'))
else:
dog_names.append('NaN')
df_weratedogs_master['dog_name'] = dog_names
Test¶
In [226]:
get_df_info(df_weratedogs_master)
Define¶
Identify dog gender and add the column
Code¶
In [227]:
male = ['He', 'he', 'him', 'his', "he's", 'himself']
female = ['She', 'she', 'her', 'hers', 'herself', "she's"]
dog_gender = []
for text in df_weratedogs_master['text']:
if any(map(lambda v:v in male, text.split())):
dog_gender.append('male')
elif any(map(lambda v:v in female, text.split())):
dog_gender.append('female')
else:
dog_gender.append('NaN')
df_weratedogs_master['dog_gender'] = dog_gender
Test¶
In [228]:
len(dog_gender)
print("dog_gender count \n", get_df_value_counts(
df_weratedogs_master.dog_gender))
Replace NaN by None
In [229]:
df_weratedogs_master.loc[df_weratedogs_master
['dog_name'] == 'NaN',
'dog_name'] = None
df_weratedogs_master.loc[df_weratedogs_master
['dog_gender'] == 'NaN',
'dog_gender'] = None
Define¶
Match the data type before storing
Code¶
In [230]:
df_weratedogs_master.dtypes
Out[230]:
In [233]:
df_weratedogs_master['tweet_id'] = df_weratedogs_master[
'tweet_id'].astype(object)
df_weratedogs_master['source'] = df_weratedogs_master[
'source'].astype('category')
df_weratedogs_master['rating_numerator'] = df_weratedogs_master[
'rating_numerator'].astype(float)
df_weratedogs_master['rating_denominator'] = df_weratedogs_master[
'rating_denominator'].astype(float)
df_weratedogs_master['favorites'] = df_weratedogs_master[
'favorites'].astype(int)
df_weratedogs_master['retweets'] = df_weratedogs_master[
'retweets'].astype(int)
df_weratedogs_master['dog_type'] = df_weratedogs_master[
'dog_type'].astype('category')
df_weratedogs_master['dog_gender'] = df_weratedogs_master[
'dog_gender'].astype('category')
df_weratedogs_master['dog_name'] = df_weratedogs_master[
'dog_name'].astype('category')
Test¶
In [234]:
df_weratedogs_master.dtypes
Out[234]:
Store Wrangled Data¶
In [235]:
df_weratedogs_master.to_csv('twitter_archive_master.csv',
index=False, encoding = 'utf-8')
In [236]:
df_weratedogs_master = pd.read_csv('twitter_archive_master.csv')
df_weratedogs_master.info()
Store it in database¶
In [238]:
engine = sqlalchemy.create_engine('sqlite:///twitter_archive_master.db')
df_weratedogs_master.to_sql('master', engine, index=False)
Read it back¶
In [239]:
df_gather = pd.read_sql('SELECT * FROM master', engine)
get_df_head(df_gather, 3)
Out[239]: